package com.yalu.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.yalu.dao.data.StoresDao;
import com.yalu.entity.data.Sales;
import com.yalu.entity.data.SalesKey;
import com.yalu.entity.data.Stores;
import com.yalu.entity.data.StoresProperties;


public class SalesExcelImportUtil extends BaseExcelImportUtil {
	private List<List<String>> str = null;
	private static final String TITLE = "时间,店铺ID,店铺名称,产品编号,SKU,成本价,销售金额,件数";
	private static int COLUMN_COUNT = 8	;
	
	private StoresDao storesDao = (StoresDao) SpringUtil
			.getBean("storesDaoImpl");
	Map<Integer, Stores> storesMap = new HashMap<Integer, Stores>();

	public SalesExcelImportUtil() {
		List<Stores> storesList = storesDao.getAll();
		for (Stores stores : storesList) {
			storesMap.put(Integer.valueOf(stores.getNo()), stores);
		}
	}

	@Override
	public List<String> importExcel(String excelFile) {
		try {
			List<List<String>> array = readExcelReturnArray(excelFile);
			return validate(array);
		} catch (Exception e) {
			e.printStackTrace();
			return errMsg;
		}

	}

	private List<String> validate(List<List<String>> array) {
		for (int rowIndex = 0; rowIndex < array.size(); rowIndex++) {
			List<String> row = array.get(rowIndex);
			if (rowIndex == 0) {
				String msg = validateTitle(row);
				if (StringUtils.isNotBlank(msg)) {
					errMsg.add(msg);
				}
			} else {
				String msg = validateData(rowIndex, row);
				if (StringUtils.isNotBlank(msg)) {
					errMsg.add(msg);
				}
			}
		}
		return errMsg;
	}

	/**
	 * @Description: 数据格式校验
	 * @param rowIndex
	 * @param row
	 * @return
	 * @date 2013-10-29 上午12:21:12
	 * @author zch
	 */
	private String validateData(int rowIndex, List<String> row) {
		Sales entity = new Sales();
		SalesKey key = new SalesKey();
		String msg = "";
		String value = "";
		for (int columnIndex = 0; columnIndex < COLUMN_COUNT; columnIndex++) {
			value = row.get(columnIndex);

			switch (columnIndex) {
			case 0:
				if (StringUtils.isNotBlank(value)) {
					entity.setDatadate(value);
				} else {
					msg = "【时间】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 1:
				if (StringUtils.isNotBlank(value)) {
					Stores stores = storesMap.get(Integer.valueOf(value));
					if (stores == null) {
						msg = "【店铺ID】栏第【" + rowIndex + "】店铺ID在平台中不存在";
						return msg;
					} else {
						entity.setStore_id(NumberUtils.toInt(value));
					}
				} else {
					msg = "【店铺ID】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 2:
				Stores stores = storesMap.get(Integer.valueOf(entity.getStore_id()));
				if (stores != null && !StringUtils.equals(stores.getName(), value)) {
					msg = "【店铺名称】栏第【" + rowIndex + "】店铺名称与店铺ID不符";
					return msg;
				} else {
					entity.setName(value);
				}
				break;
			case 4:
				String formulaValue = StringUtils.substring(value, 0, 7);
				
				if (StringUtils.isNotBlank(value)) {
					key.setSku(value);
				} else {
					msg = "【SKU】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}

				if (StringUtils.isNotBlank(formulaValue)) {
					key.setProduct_id(formulaValue);
				} else {
					msg = "【产品编号】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				
				break;
			case 5:
				if (StringUtils.isNotBlank(value)) {
					entity.setBase_prices(NumberUtils.toFloat(value));
				} else {
					msg = "【成本价】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 6:
				if (StringUtils.isNotBlank(value)) {
					entity.setSell_prices(NumberUtils.toFloat(value));
				} else {
					msg = "【销售金额】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 7:
				if (StringUtils.isNotBlank(value)) {
					entity.setItems(NumberUtils.toInt(value));
				} else {
					msg = "【件数】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			}
			entity.setSalesKey(key);
		}
		dataList.add(entity);
		return null;
	}

		/**
		 * @Description: 校验标题行
		 * @param row
		 * @return
		 * @date 2013-10-29 上午12:19:39
		 * @author zch
		 */
		private String validateTitle(List<String> row) {
			String msg = "";
			String title = "";
			for (int index = 0; index < COLUMN_COUNT; index++) {
				if (index != COLUMN_COUNT - 1) {
					title = title + row.get(index) + ",";
				} else {
					title = title + row.get(index);
				}

			}
			if (!title.equals(TITLE)) {
				msg = "首行标题依次应为--" + TITLE;
				return msg;
			} else {
				return null;
			}
		}

		public List<List<String>> readExcelReturnArray(String excelFile)
				throws Exception {
			List<List<String>> array = new ArrayList<List<String>>();
			InputStream stream = null;
			try {
				stream = new FileInputStream(excelFile);
				XSSFWorkbook myWorkBook = new XSSFWorkbook(stream);
				XSSFSheet mySheet = myWorkBook.getSheetAt(0);
				Iterator<Row> rowIter = mySheet.rowIterator();
				String cellValue = "";
				int rowIndex = 0;
				while (rowIter.hasNext()) {
					XSSFRow myRow = (XSSFRow) rowIter.next();
					if(myRow==null){
						continue;
					}
					// Iterator<Cell> cellIter = myRow.cellIterator();
					List<String> rowArr = new ArrayList<String>();

					for (int cellnum = 0; cellnum <= COLUMN_COUNT; cellnum++) {
						cellValue = "";
						XSSFCell myCell = myRow.getCell(cellnum);
						if (myCell == null) {
							rowArr.add(cellValue.trim());
							continue;
						}
						// int columnIndex = myCell.getColumnIndex();

						switch (myCell.getCellType()) {
						case XSSFCell.CELL_TYPE_NUMERIC:
							Double numCellValue = myCell.getNumericCellValue();
							boolean isDate = DateUtil.isCellDateFormatted(myCell);
							if (DateUtil.isCellDateFormatted(myCell)) {
								Date t = myCell.getDateCellValue();
								SimpleDateFormat format = new SimpleDateFormat(
										"yyyyMMdd");
								cellValue = format.format(t);
							} else {
								cellValue = numCellValue.intValue() + "";//日期特殊处理
							}
							break;
						case XSSFCell.CELL_TYPE_STRING:
							String strCellString = myCell.getStringCellValue();
							cellValue = strCellString;
							break;
						case XSSFCell.CELL_TYPE_BLANK:
							break;
						}
						rowArr.add(cellValue.trim());
					}
					for (int columnIndex = COLUMN_COUNT; columnIndex > rowArr
							.size();) {
						rowArr.add("");
					}
					//不加入空行数据
					for(String str : rowArr){
						if(!str.equals("")){
							array.add(rowArr);
							break;
						}
					}
					rowIndex++;
				}
			} catch (FileNotFoundException e) {
				e.printStackTrace();
				throw e;
			} catch (IOException e) {
				e.printStackTrace();
				throw e;
			} finally {
				if (stream != null) {
					try {
						stream.close();
					} catch (IOException e1) {
						e1.printStackTrace();
					}
				}
			}

			return array;
		}

		public List<List<String>> getStr() {
			return str;
		}

		public void setStr(List<List<String>> str) {
			this.str = str;
		}

	}
